﻿Public Class clsDbLotInfo
    Public Structure ProcessCode
        Dim szNowProcess As String
        Dim szNowName As String
        Dim szNextProcess As String
        Dim szNextName As String
    End Structure

    Public Sub GetMetialInfo()

    End Sub

    Public Function GetProcessCD(ByVal szLotNo As String) As ProcessCode
        Dim PCd As New ProcessCode
        Dim szSql As String = String.Empty

        szSql &= "SELECT LOT.KOUTEI_CODE AS NOW_KOUTEI,CD1.COMMENT_1 AS NOW_NAME "
        szSql &= "FROM PER.L_TAG_LOTDATA LOT "
        szSql &= "LEFT OUTER JOIN SCH.T_KOUTEI_CODE_INFO CD1 "
        szSql &= "ON CD1.KOUTEI_CODE=LOT.KOUTEI_CODE "
        szSql &= "WHERE LOT.LOT_NO='" & szLotNo & "' "


        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1)
        If Not IsNothing(dt) AndAlso dt.Rows.Count > 0 Then
            PCd.szNowProcess = IIf(IsDBNull(dt.Rows(0)("NOW_KOUTEI")), String.Empty, dt.Rows(0)("NOW_KOUTEI"))
            PCd.szNowName = IIf(IsDBNull(dt.Rows(0)("NOW_NAME")), String.Empty, dt.Rows(0)("NOW_NAME"))

            szSql = ""
            szSql = "SELECT * FROM ("
            szSql &= "SELECT CD.KOMOKUDATA AS NEXT_KOUTEI, NM.PROC_NAME AS NEXT_NAME "
            szSql &= "FROM PER.L_JYOKENDATA CD "
            szSql &= "INNER JOIN PER.T_PROC_CD NM "
            szSql &= "ON CD.KOMOKUDATA = NM.PROC_CD "
            szSql &= "WHERE ( "
            szSql &= " SELECT TO_NUMBER(KOMOKUNAME,'99') "
            szSql &= " FROM PER.L_JYOKENDATA "
            szSql &= " WHERE KOMOKUDATA = '" & PCd.szNowProcess & "' "
            szSql &= " AND KLOTNO = '{0}' "
            szSql &= " AND KOTEI = 'PROCESS_INFO' "
            szSql &= " )  < TO_NUMBER(KOMOKUNAME, '99') "
            szSql &= "AND KLOTNO = '{0}' "
            szSql &= "AND KOTEI = 'PROCESS_INFO' "
            'szSql &= "AND ROWNUM <= 1 " 'ADD MIAO 2018/10/17 DEL MIAO 2019/1/1

            szSql &= "ORDER BY "
            szSql &= "TO_NUMBER(KOMOKUNAME, '99') ASC "
            szSql &= ") WHERE    ROWNUM <= 1" 'ADD MIAO 2019/1/1

            'szSql &= "LIMIT 1;"'DEL MIAO 2018/10/17

            Dim dtProc As DataTable = DBacs.ExecuteSql(String.Format(szSql, szLotNo), -1)
            If Not IsNothing(dtProc) AndAlso dtProc.Rows.Count > 0 Then
                PCd.szNextProcess = IIf(IsDBNull(dtProc.Rows(0)("NEXT_KOUTEI")), String.Empty, dtProc.Rows(0)("NEXT_KOUTEI"))
                PCd.szNextName = IIf(IsDBNull(dtProc.Rows(0)("NEXT_NAME")), String.Empty, dtProc.Rows(0)("NEXT_NAME"))
            Else
                PCd.szNextProcess = ""
                PCd.szNextName = ""
            End If
        Else
            PCd.szNowProcess = ""
            PCd.szNowName = ""
            PCd.szNextProcess = ""
            PCd.szNextName = ""
        End If

        Return PCd
    End Function

    '>> 2018/02/20 安部 iniファイル整理
    ' エラーメッセージ取得
    'Public Function GetAllMsg(ByVal lang As String) As DataTable
    '    Dim szSql As String = ""
    '    szSql &= "SELECT MESSAGE_ID, MESSAGE"
    '    szSql &= "  FROM PER.T_MESSAGE_MST "
    '    szSql &= "  WHERE LANGUAGE = '" & lang & "' "
    '    szSql &= "  ORDER BY MESSAGE_ID"

    '    Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1)
    '    If IsNothing(dt) Then
    '        GetAllMsg = New DataTable
    '    Else
    '        GetAllMsg = dt
    '    End If

    'End Function
    '<< 2018/02/20 安部 iniファイル整理

    '>>20171012 兪
    Public Function SaveTagLotInfo(ByVal tagData As frmStartCompleteBase.TagData, setXin As Integer,
                                   Optional ByVal inputLotType As String = frmStartCompleteBase.INPUTLOTTYPE_ASM) As Integer
        Dim szSql As String = ""
        With tagData
            szSql &= "UPDATE per.l_tag_lotdata SET "
            'szSql &= "  wafer_lotno = '" & .szDifLotNo & "' "
            szSql &= "  status = " & CInt(.Status) & " "
            szSql &= "  , equipno = '" & .szEquipID & "' "
            szSql &= "  , koutei_code = '" & .szProcsCD & "' "
            szSql &= "  , invnum = " & .iInput & " "
            szSql &= "  , compnum = " & .iFinish & " "
            szSql &= "  , badnum = " & .iFailed & " "
            szSql &= "  , framenum = " & .iFrame & " "
            'ADD MIAOJF 20200729 BEGIN
            If setXin = 1 And .iFrameIDUse = 1 Then
                szSql &= "  , BADMARKNUM = " & .iCross & " "
                szSql &= "  , BADMARK_FRAMENUM = " & .iCrossFrm & " "
                szSql &= "  , EMPTY_CHIP_NUM = " & .emptyChipNum & " "
            End If
            'ADD MIAOJF 20200729 END
            'szSql &= "  , badmarknum = " & .iCross & " "
            szSql &= "  , q_flg = " & If(.fQflag, 1, 0) & " "
            szSql &= "  , test_flg = " & If(.fTestflag, 1, 0) & " "
            szSql &= "  , check_flg = " & If(.fCheckflag, 1, 0) & " "
            '>>20171010 兪
            szSql &= "  , holdstatus = " & CByte(.OnHoldSt) & " "
            '<<20171010 兪
            Select Case inputLotType
                '>>20180112 佐川
                'Case frmStartCompleteBase.INPUTLOTTYPE_DIFF
                'szSql &= "WHERE  wafer_lotno = '" & .szDifLotNo & "' "
                '<<20180112 佐川
                Case Else
                    szSql &= "WHERE  lot_no = '" & .szLotNo & "' "
            End Select

        End With

        Return DBacs.ExecuteUpdateSql(szSql, -1)
    End Function
    '<<20171012 兪

    'ADD 苗 2018/1/26 BEGIN フレームID対応
    'atlasのFrameMAP画面で更新したフレーム数、X印数、X印枚数を取得する
    Public Function getAtlasUpdatedNum(ByVal lotNo As String) As DataTable
        Dim szSql As String = ""
        'UPD 苗 2018/1/31
        szSql &= "SELECT ATLAS_FRAMENUM, ATLAS_BADMARKNUM,  ATLAS_BADMARK_FRAMENUM, ATLAS_EMPTYCHIPS_NUM "
        szSql &= "  FROM PER.L_TAG_LOTDATA "
        szSql &= "  WHERE LOT_NO = '" & lotNo & "' "

        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1)
        If IsNothing(dt) Then
            Return New DataTable
        Else
            Return dt
        End If

    End Function
    'ADD 苗 2018/1/26 END フレームID対応

    '>> 2018/01/30 佐川 
    Public Function SaveLotInfoTagEditor(ByVal tagData As frmStartCompleteBase.TagData,
                               Optional ByVal inputLotType As String = frmStartCompleteBase.INPUTLOTTYPE_ASM) As Integer
        Dim szSql As String = ""
        With tagData
            szSql &= "UPDATE per.l_tag_lotdata SET "
            szSql &= "  status = " & CInt(.Status) & " "
            szSql &= "  , wafer_lotno = '" & .szDifLotNo & "' " 'ADD 苗 2018/4/2
            szSql &= "  , equipno = '" & .szEquipID & "' "
            szSql &= "  , koutei_code = '" & .szProcsCD & "' "
            szSql &= "  , invnum = " & .iInput & " "
            szSql &= "  , compnum = " & .iFinish & " "
            szSql &= "  , badnum = " & .iFailed & " "
            szSql &= "  , framenum = " & .iFrame & " "
            szSql &= "  , badmarknum = " & .iCross & " "
            szSql &= "  , badmark_framenum = " & .iCrossFrm & " "
            szSql &= "  , q_flg = " & If(.fQflag, 1, 0) & " "
            szSql &= "  , test_flg = " & If(.fTestflag, 1, 0) & " "
            szSql &= "  , check_flg = " & If(.fCheckflag, 1, 0) & " "
            szSql &= "  , holdstatus = " & CByte(.OnHoldSt) & " "
            szSql &= "  , container1 = '" & .container1 & "' "
            szSql &= "  , container2 = '" & .container2 & "' "
            szSql &= "  , container3 = '" & .container3 & "' "
            szSql &= "  , container4 = '" & .container4 & "' "
            szSql &= "  , container5 = '" & .container5 & "' "
            '>>2018/2/16 大賀 No74 ICタグ編集項目追加対応
            szSql &= "  , empty_chip_num = " & .emptyChipNum & " "
            szSql &= "  , plt_equipno = '" & .plateEquipNo & "' "
            szSql &= "  , frame_ID_use = " & If(.frameIDFlag, 1, 0) & " "
            '<<2018/2/16 大賀 No74 ICタグ編集項目追加対応
            '>>20180302 大賀 GroupKey入力時ウェハ枚数入力
            szSql &= "  , wafer_num = " & .waferNum & " "
            '<<20180302 大賀 GroupKey入力時ウェハ枚数入力
            szSql &= "WHERE  lot_no = '" & .szLotNo & "' "

        End With

        Return DBacs.ExecuteUpdateSql(szSql, -1)
    End Function
    '<< 2018/01/30 佐川

    '>> 20180330 大賀 No128 IcTag編集画面履歴の作成
    Public Enum CHANGE_TYPE
        BEFORE
        AFTER
    End Enum

    ' ICタグ履歴からシーケンス番号を取得する
    Private Function getLotInfoHistorySeq(ByVal LotNo As String, ByVal type As Integer) As Integer
        Dim szSql As String = ""
        Dim seqNum = 0
        szSql &= "SELECT  SEQ "
        szSql &= "FROM PER.L_TAG_LOTDATA_HISTORY "
        szSql &= "WHERE LOT_NO = ? AND CHANGE_TYPE = ? "

        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, LotNo, 0) '取得は必ず変更前
        If dt.Rows.Count > 0 Then
            seqNum = dt.AsEnumerable.Max(Function(x) x("SEQ"))
        End If
        If type = CHANGE_TYPE.BEFORE Then
            seqNum += 1
        End If
        Return seqNum
    End Function

    ' ICタグ履歴に書き込みを行う
    Public Function SaveLotInfoTagHistory(ByVal tagData As frmStartCompleteBase.TagData, ByVal chengeType As Integer, ByVal optTime As Date,
                                          ByVal worker As String, ByVal equipNo As String, ByVal TerminalName As String, ByVal log As String) As Integer
        Dim ret = 0
        Dim seq = getLotInfoHistorySeq(tagData.szLotNo, chengeType)

        Dim szSql = String.Empty
        szSql &= "INSERT INTO PER.L_TAG_LOTDATA_HISTORY( "
        szSql &= "  LOT_NO, WAFER_LOTNO,"
        szSql &= "  SEQ, CHANGE_TYPE, OPT_TIME, OPT_OPERATOR, OPT_EQUIPNO, OPT_TERMINAL_NAME, OPT_CHANGE_LOG,"
        szSql &= "  STATUS, EQUIPNO, KOUTEI_CODE, "
        szSql &= "  INVNUM, BADNUM, COMPNUM, FRAMENUM, BADMARKNUM, BADMARK_FRAMENUM, "
        szSql &= "  Q_FLG, TEST_FLG, CHECK_FLG, HOLDSTATUS, "
        szSql &= "  CONTAINER1, CONTAINER2, CONTAINER3, CONTAINER4, CONTAINER5, "
        szSql &= "  EMPTY_CHIP_NUM, PLT_EQUIPNO, FRAME_ID_USE, WAFER_NUM "
        szSql &= ") "
        szSql &= "VALUES ("
        szSql &= "  ?, ?,"
        szSql &= "  ?, ?, ?, ?, ?, ?, ?,"
        szSql &= "  ?, ?, ?,"
        szSql &= "  ?, ?, ?, ?, ?, ?,"
        szSql &= "  ?, ?, ?, ?,"
        szSql &= "  ?, ?, ?, ?, ?,"
        szSql &= "  ?, ?, ?, ? ) "
        With tagData
            ret = DBacs.ExecuteUpdateSql(szSql, -1,
                                          .szLotNo, .szDifLotNo,
                                          seq, chengeType, optTime, worker, equipNo, TerminalName, log,
                                           CInt(.Status), .szEquipID, .szProcsCD,
                                          .iInput, .iFailed, .iFinish, .iFrame, .iCross, .iCrossFrm,
                                          If(.fQflag, 1, 0), If(.fTestflag, 1, 0), If(.fCheckflag, 1, 0), CByte(.OnHoldSt),
                                          .container1, .container2, .container3, .container4, .container5,
                                          .emptyChipNum, .plateEquipNo, If(.frameIDFlag, 1, 0), .waferNum)
        End With
        Return ret
    End Function
    '<< 20180330 大賀 No128 IcTag編集画面履歴の作成
    Public Function getSumLotInvnum(WAFER_LOTNO As String) As Integer
        Dim szSql As String = ""
        Dim sumNum As Integer = 0
        szSql &= "SELECT SUM(INVNUM) AS INVNUMSUM "
        szSql &= "FROM PER.L_TAG_LOTDATA "
        szSql &= "WHERE WAFER_LOTNO = ? "

        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, WAFER_LOTNO)
        If dt.Rows.Count > 0 Then
            sumNum = dt.Rows(0)("INVNUMSUM")
        End If

        Return sumNum
    End Function

    Public Function SaveTagLotInfo(lotno As String, proccd As String) As Integer
        Dim szSql As String = ""
        szSql &= "UPDATE per.l_tag_lotdata SET "
        szSql &= "  koutei_code = '" & proccd & "' "
        szSql &= "WHERE  lot_no = '" & lotno & "' "

        Return DBacs.ExecuteUpdateSql(szSql, -1)
    End Function

    Public Function isCar(lotNo As String) As Boolean
        Dim szSql As String = ""
        Dim rtn As Boolean = False
        szSql &= "SELECT KOMOKUDATA FROM PER.L_JYOKENDATA where KLOTNO ='" & lotNo & "' "
        szSql &= " and  KOTEI = 'PROCESS_INFO' and KOMOKUDATA = 'A12J'"

        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1)
        If dt.Rows.Count > 0 AndAlso dt.Rows(0)(0) = "A12J" Then
            rtn = True
        Else
            rtn = False
        End If

        Return rtn

    End Function

End Class
